/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sigbar.access;


import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;


/**
 *
 * @author hdhzero
 */
public class BDInterface {

    public static int CONNECTION_ERROR = 0;
    public static int GERENTE_CONNECTED = 1;
    public static int SUPERVISOR_CONNECTED = 2;
    public static int VENDEDOR_CONNECTED = 4;
    private String current_login;
    private String current_password;
    private static String URL = "jdbc:postgresql://localhost:5432/aztech";

    public int login(String login, String senha) {
        int type = 0;
        try {
            //Realizar conexção com o BD.
            Connection con = DriverManager.getConnection(URL, login, senha);
            Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String query = "SELECT funcionario.vendedor, funcionario.gerente, funcionario.garcom, funcionario.superestoque " +
                    "FROM aztech.funcionario, aztech.funclogin " +
                    "WHERE funclogin.funcionario_cpffunc = funcionario.cpffunc AND aztech.funclogin.login = '" + login + "'"; //TODO: Trocar a query por uma que presta.
            ResultSet result = s.executeQuery(query);
            boolean isVendedor = false;
            boolean isGerente = false;
            boolean isSuper = false;
            if (result.first()) {
                isVendedor = result.getBoolean(1);
                isGerente = result.getBoolean(2);
                isSuper = result.getBoolean(4);
            } else {
                return 0;
            }
            if (isVendedor) {
                type += VENDEDOR_CONNECTED;
            }
            if (isGerente) {
                type += GERENTE_CONNECTED;
            }
            if (isSuper) {
                type += SUPERVISOR_CONNECTED;
            }
        } catch (SQLException ex) {
            System.out.println(ex.getSQLState());
            System.out.print(ex.getMessage());
            return CONNECTION_ERROR;
        }
        current_login = login;
        current_password = senha;
        return type;
    }

    public boolean cadastrarFornecedorFisico(String cpf, String nome, Vector<String> emails, Vector<String> fones){
        boolean resp = false;
        try {
            Connection con = DriverManager.getConnection(URL, current_login, current_password);
            Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String query = "SELECT fornecedor.idfornecedor " +
                    "FROM aztech.fornecedor " +
                    "ORDER BY fornecedor.idfornecedor DESC"; //TODO: Trocar a query por uma que presta.
            ResultSet result = s.executeQuery(query);
            int lastID;
            if (result.first())
                lastID = result.getInt(1);
            else lastID = -1;
            int ID = lastID + 1;

            query = "INSERT INTO aztech.fornecedor(idfornecedor) "+
                    "VALUES ("+ID+")";
            s.execute(query);

            query = "INSERT INTO aztech.mempesfisica(cpf) "+
                    "VALUES ('"+cpf+"')";
            s.execute(query);

            query = "INSERT INTO aztech.pessoafisica(nome, mempesfisica_cpf) "+
                    "VALUES ('"+nome+"', '"+cpf+"')";
            s.execute(query);

            query = "INSERT INTO aztech.fornecedorpessoafisica(fornecedor_idfornecedor, mempesfisica_cpf) "+
                    "VALUES ("+ID+", '"+cpf+"')";
            s.execute(query);

            while (!emails.isEmpty()){
                String email = emails.firstElement();
                emails.removeElementAt(0);

                query = "INSERT INTO aztech.emailfornecedor(email, fornecedor_idfornecedor) "+
                        "VALUES ('"+email+"', "+ID+")";
                s.execute(query);
            }

            while (!fones.isEmpty()){
                String fone = fones.firstElement();
                fones.removeElementAt(0);

                query = "INSERT INTO aztech.fonefornecedor(fone, fornecedor_idfornecedor) "+
                        "VALUES ('"+fone+"', "+ID+")";
                s.execute(query);
            }

            resp = true;
        } catch (SQLException ex) {
            resp = false;
        }
        return resp;
    }

    public boolean cadastrarFornecedorJuridico(String cnpj,String nomeemp,String nomefantas, Vector<String> emails, Vector<String> fones){
        boolean resp = false;
        try {
            Connection con = DriverManager.getConnection(URL, current_login, current_password);
            Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String query = "SELECT fornecedor.idfornecedor " +
                    "FROM aztech.fornecedor " +
                    "ORDER BY fornecedor.idfornecedor DESC"; //TODO: Trocar a query por uma que presta.
            ResultSet result = s.executeQuery(query);
            int lastID;
            if (result.first())
                lastID = result.getInt(1);
            else lastID = -1;
            int ID = lastID+1;

            query = "INSERT INTO aztech.fornecedor(idfornecedor) "+
                    "VALUES ("+ID+")";
            s.execute(query);

            query = "INSERT INTO aztech.pessoajur(cnpj, fornecedor_idfornecedor, nomeemp, nomefantas) "+
                    "VALUES ('"+cnpj+"', "+ID+", '"+nomeemp+"', '"+nomefantas+"')";
            s.execute(query);

            while (!emails.isEmpty()){
                String email = emails.firstElement();
                emails.removeElementAt(0);

                query = "INSERT INTO aztech.emailfornecedor(email, fornecedor_idfornecedor) "+
                        "VALUES ('"+email+"', "+ID+")";
                s.execute(query);
            }

            while (!fones.isEmpty()){
                String fone = fones.firstElement();
                fones.removeElementAt(0);

                query = "INSERT INTO aztech.fonefornecedor(fone, fornecedor_idfornecedor) "+
                        "VALUES ('"+fone+"', "+ID+")";
                s.execute(query);
            }

            resp = true;
        } catch (SQLException ex) {
            resp = false;
        }

        return resp;
    }
    public boolean cadastrarProduto(String nome,double valcompra,int limite,double valvenda,int quantid){
        boolean resp = false;
        try {
            Connection con = DriverManager.getConnection(URL, current_login, current_password);
            Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String query = "INSERT INTO aztech.produto(nome, valcompra, limite, valvenda, quantid) "+
                           "VALUES ('"+nome+"', "+valcompra+", "+limite+", "+valvenda+", "+quantid+")";
            s.executeQuery(query);

            resp = true;
        } catch (SQLException ex) {
            resp = false;
        }

        return resp;
    }

    public boolean atualizarProduto(String velhoNome,String nome,double valcompra,int limite,double valvenda,int quantid){
        boolean resp = false;
        try {
            Connection con = DriverManager.getConnection(URL, current_login, current_password);
            Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String query = "UPDATE aztech.produto SET nome='"+nome+"', valcompra="+valcompra+", limite="+limite+", valvenda="+valvenda+", quantid="+quantid+" "+
                           "WHERE produto.nome = '"+velhoNome+"'";
            s.executeQuery(query);

            resp = true;
        } catch (SQLException ex) {
            resp = false;
        }

        return resp;
    }

    public Vector<String> buscarProduto(String nome){
        Vector<String> resp = null;

        try {
            Connection con = DriverManager.getConnection(URL, current_login, current_password);
            Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String query = "SELECT * FROM aztech.produto WHERE produto.nome = '"+nome+"'";
            ResultSet result = s.executeQuery(query);
            result.first();

            resp = new Vector<String>();
            resp.add(result.getString(1));
            resp.add(result.getString(2));
            resp.add(result.getString(3));
            resp.add(result.getString(4));
            resp.add(result.getString(5));
        } catch (SQLException ex) {
            resp = null;
        }

        return resp;
    }

    public Vector<Vector<String>> buscarProdutos(){
        Vector<Vector<String>> resp = null;

        try {
            Connection con = DriverManager.getConnection(URL, current_login, current_password);
            Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String query = "SELECT * FROM aztech.produto";
            ResultSet result = s.executeQuery(query);

            resp = new Vector<Vector<String>>();
            while(result.next()){
                Vector<String> v = new Vector<String>();
                v.add(result.getString(1));
                v.add(result.getString(2));
                v.add(result.getString(3));
                v.add(result.getString(4));
                v.add(result.getString(5));
                resp.add(v);
            }
        } catch (SQLException ex) {
            resp = null;
        }

        return resp;
    }

    public Vector<String> buscarHistorico(Date data){
        Vector<String> resp = null;

        try {
            Connection con = DriverManager.getConnection(URL, current_login, current_password);
            Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String query = "";
            ResultSet result = s.executeQuery(query);
            result.first();

            resp = new Vector<String>();
            resp.add(result.getString(1));
            resp.add(result.getString(2));
            resp.add(result.getString(3));
        } catch (SQLException ex) {
            resp = null;
        }

        return resp;
    }

    public Vector<String> buscarNomeFornecedores(){
        Vector<String> resp = null;

        try {
            Connection con = DriverManager.getConnection(URL, current_login, current_password);
            Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            String query = "";
            ResultSet result = s.executeQuery(query);

            while(result.next()){
                if (result.getString(1) == null)resp.add(result.getString(2));
                else resp.add(result.getString(1));
            }

        } catch (SQLException ex) {
            resp = null;
        }

        return resp;
    }

    public boolean cadastrarFuncionario2(String cpf,double salario,boolean vendedor,boolean gerente,boolean garcom,boolean superestoque,String expediente){
        boolean resp = false;

        try {
            Connection con = DriverManager.getConnection(URL, current_login, current_password);
            Statement s = con.createStatement();
            String query = "INSERT INTO aztech.funcionario(cpffunc, salario, vendedor, gerente, garcom, superestoque, expediente) "+
                           "VALUES ('"+cpf+"', "+salario+", "+vendedor+", "+gerente+", "+garcom+", "+superestoque+", '"+expediente+"')";
            s.executeQuery(query);

            resp = true;
        } catch (SQLException ex) {
            resp = false;
        }

        return resp;
    }

   // public Vector<String> buscarFuncionario(){

    //}
}